﻿using DAL.Register;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Utils;

namespace DAL
{
    /// <summary>
    /// 图书管理数据访问类
    /// </summary>
    public class BookManagementDAL
    {
        /// <summary>
        /// 图书分类
        /// </summary>
        /// <returns></returns>
        public DataTable ShowDAL()
        {
            string sql = $"select ID,CategoryID,CategoryName,ParentID from BookCategory";
            return SQLHelper.Query(sql,null);
        }

      
        /// <summary>
        /// 获取父类
        /// </summary>
        /// <returns></returns>
        public DataTable FindParentDAL()
        {
            string sql = "SELECT * FROM BookCategory WHERE ParentID=@Parentid";
            SqlParameter[] par = { new SqlParameter("@Parentid", "0") };
            return SQLHelper.Query(sql, par);
        }

        /// <summary>
        /// 获取子类
        /// </summary>
        /// <returns></returns>
        public DataTable FindsonDAL()
        {
            string sql = "SELECT * FROM BookCategory WHERE ParentID!=@Parentid";

            SqlParameter[] par = { new SqlParameter("@Parentid", "0") };
            return SQLHelper.Query(sql, par);
        }

        /// <summary>
        /// 图书列表及搜索
        /// </summary>
        /// <param name="BookID">图书id</param>
        /// <returns></returns>
        public DataTable getBookbyidDAL(string BookID)
        {
            string sqls = BookID == "" ? "" : "WHERE b.BookID = @BookID";
            string sql = @"SELECT b.ID 编号,b.Name 图书名称,a.CategoryName 图书类别,b.Author 作者,b.Money 价格,b.BookID 图书id,c.Description 借出状态 
                            FROM BookCategory as a
                            INNER JOIN BookInfo  b
                            ON a.ID= b.CategoryType
                            LEFT OUTER JOIN BookState  c
                            ON c.ID=b.State "+sqls;
            SqlParameter[] sqlp = {
                    new SqlParameter("@BookID",BookID),
                };
            return SQLHelper.Query(sql, BookID != "" ? sqlp : null);
        }


        /// <summary>
        /// 添加图书
        /// </summary>
        /// <returns></returns>
        public int AddBookDAL(BookInfo book)
        {
            string sql = "INSERT INTO BookInfo(BookID,Name,CategoryType,Money,Author,State) VALUES (@BookID,@Name,@CategoryType,@Money,@Author,@State)";

            SqlParameter[] add = {
                new SqlParameter("@BookID",book._bookId),
                new SqlParameter("@Name", book._name),
                new SqlParameter("@CategoryType", book._categoryType),
                new SqlParameter("@Money", book._money),
                new SqlParameter("@Author", book._author),
                new SqlParameter("@State", "0")
            };
            return SQLHelper.Close(sql, add);
        }

        /// <summary>
        /// 修改图书
        /// </summary>
        /// <returns></returns>
        public int UpdateBookDAL(BookInfo book)
        {
            string sql = @"UPDATE BookInfo
                           SET Name=@Name,CategoryType=@CategoryType,Author=@Author,Money=@Money,BookID=@BookID
                           WHERE ID=@ID;";
            SqlParameter[] update = {
                new SqlParameter("@ID",book._Id),
                new SqlParameter("@Name",book._name),
                new SqlParameter("@CategoryType", book._categoryType),
                new SqlParameter("@Author", book._author),
                new SqlParameter("@Money", book._money),
                new SqlParameter("@BookID", book._bookId),
            };
            return SQLHelper.Close(sql, update);
        }

        /// <summary>
        /// 删除图书
        /// </summary>
        /// <returns></returns>
        public int DeleteBookDAL(string bookid)
        {
            string sql = "DELETE FROM BookInfo WHERE BookID=@BookID";

            SqlParameter[] de = {
                new SqlParameter("@BookID",bookid),
            };
            return SQLHelper.Close(sql, de);
        }


        /// <summary>
        /// 树目录搜索
        /// </summary>
        /// <param name="CategoryID">分类id</param>
        /// <returns></returns>
        public DataTable getBookCategoryDAL(string CategoryID)
        {
            string sql = @"SELECT b.ID 编号,b.Name 图书名称,a.CategoryName 图书类别,b.Author 作者,b.Money 价格,b.BookID 图书id,c.Description 借出状态 
                            FROM BookCategory as a
                            INNER JOIN BookInfo  b
                            ON a.ID= b.CategoryType
                            LEFT OUTER JOIN BookState  c
                            ON c.ID=b.State WHERE b.CategoryType = @CategoryType";
            SqlParameter[] sqlp = {
                    new SqlParameter("@CategoryType",CategoryID),
                };
            return SQLHelper.Query(sql, sqlp);
        }

        /// <summary>
        /// 根据图书id查询该书是否被借出
        /// </summary>
        /// <param name="BookID">图书id</param>
        /// <returns></returns>
        public DataTable getBookBorrowDAL(string BookID)
        {
            string sql = @"SELECT * FROM BookBorrowReturnInfo WHERE BookID=@BookID and  ReturnTime IS NULL";
            SqlParameter[] sqlp = {
                    new SqlParameter("@BookID",BookID),
                };
            return SQLHelper.Query(sql, sqlp);
        }

        /// <summary>
        /// 查询图书id是否存在
        /// </summary>
        /// <param name="BookID"></param>
        /// <returns></returns>
        public DataTable getByBookidDAL(string BookID)
        {
            string sql = @"SELECT * FROM BookInfo WHERE BookID=@BookID ";
            SqlParameter[] sqlp = {
                    new SqlParameter("@BookID",BookID),
                };
            return SQLHelper.Query(sql, sqlp);
        }
    }
}
